Livestore architecture questions for a nutrition and excercise tracking app

[samducker]

Hey all,

I'm building a mobile fitness/health app (iOS/Android) with AI coaching.

Features include:

• Track meals with photos/voice/natural language for macronutrients analysis with AI
• Natural language workout generation (accommodating preferences/injuries) with AI
• Trend analysis of past workouts/nutrition with charts with an AI chatbot among a whole host of other tools
• But also many offline capabilities for diary logs, and manual meal and workout tracking without ai etc

I came to livestore for the last point of course! As the future looks very promising and I was much more attracted to the model of it being actually SQLite on the device vs other alternatives.

This sort of app is a single player power user type app. So I feel like a multi-tenant db per user architecture is going to be good for me. DB's can get quite big on an individual basis and social user interaction features are not really needed (or likely to be minimal) so it makes scaling easy without the problems per tenant dbs have in social apps.

Ideally in the longer term I would have the following

• Each client has a copy of the db stored on there device
• Each client has there own instance of a CF durable object which has the full mutation log but also a full copy of the db inside of it with durable object sqlite storage. (the DO is the source of truth/sync engine but also a client itself with its own full copy of the db embedded)

This would be great as then the AI can run inside of the durable object and make queries and mutations on the SQL db just like the user. As well as all of the obvious local first/offline benefits on the client.

I can even easily route the user remotely using the cloud db! (see betos example linear-lite)

I believe this sort of setup is not possible at the current time even with 0.0.3.

So I am analysing what would be best for the here and today. Here are some potential options with the questions and thoughts I have below.

• Use the node adapter

This could work, but is this just my sync layer or do I also need to host client dbs somewhere else?

• User the electric SQL sync option

I've read the docs it seems to rely on shapes and partials and seems to look towards a monolithic db -> many clients. I think a per user db is simpler in my setup and also allows those AI agent reading the db capabilities alot better.

• Use the d1 CF sync example and somehow use both the mobile app as a client but also every durable object instance as a client to update there inner sqlite db.

How difficult will this be to achieve what I'm looking for which is client on device, full db client in cloud (which can either sit next to a mutation log or not).

--

Would be grateful for your advice on how to best implement this!